package com.blue.waffle.iwc.domains.helpers;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.blue.waffle.iwc.domains.Difficulty;
import com.blue.waffle.iwc.utils.LruCache;

public class DifficultyHelper implements DataHelper<Difficulty>{

	private Connection connection;
	private static final String TABLE_NAME="difficulty";
	private PreparedStatement selectStatement;
	private PreparedStatement updateStatement;
	private PreparedStatement insertStatement;
	private PreparedStatement deleteStatement;
	private static final String SELECT_ALL_SQL="SELECT * FROM "+TABLE_NAME;
	private static LruCache<Integer, Difficulty> objectCache;
	private static final String INSERT_SQL="INSERT INTO "+TABLE_NAME+" (name,description) VALUES (?,?)";
	private static final String UPDATE_SQL="UPDATE "+TABLE_NAME+" SET name=?, description=?, progress=? WHERE id=?";
	private static final String DELETE_SQL="DELETE FROM "+TABLE_NAME+" WHERE id=?";
	private static final String SELECT_BY_ID_SQL=SELECT_ALL_SQL+" WHERE id=?";

	public DifficultyHelper(Connection connection) {
		this.connection=connection;
		if (objectCache==null) {
			objectCache=new LruCache<Integer, Difficulty>(10);
		}
	}
	
	@Override
	public List<Difficulty> selectAll() throws SQLException {
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(SELECT_ALL_SQL);
		List<Difficulty> list = new ArrayList<Difficulty>();
		while(resultSet.next()) {
			list.add(getObject(resultSet));
		}
		resultSet.close();
		return list;
	}

	@Override
	public List<Difficulty> select(String sql, Object[] para)
			throws SQLException {
		return null;
	}

	public Difficulty selectById(int id) throws SQLException {
		selectStatement=connection.prepareStatement(SELECT_BY_ID_SQL);
		selectStatement.setInt(1, id);
		ResultSet resultSet=selectStatement.executeQuery();
		if (resultSet.next()) {
			Difficulty difficulty= getObject(resultSet);
			resultSet.close();
			selectStatement.close();
			return difficulty;
		}else {
			selectStatement.close();
			return null;
		}
	}
	
	@Override
	public void insert(Difficulty model) throws SQLException {
		insertStatement = connection.prepareStatement(INSERT_SQL);
		insertStatement.setString(1, model.getName());
		insertStatement.setString(2, model.getDescription());
		insertStatement.executeUpdate();
		Statement statement = connection.createStatement();
		ResultSet keyGenerated = statement.executeQuery("SELECT last_insert_rowid()");
		if(keyGenerated.next()) {
			int id = keyGenerated.getInt(1);
			model.setId(id);
			objectCache.put(id, model);
		}
		keyGenerated.close();
		insertStatement.close();
	}

	@Override
	public void update(Difficulty model) throws SQLException {
		updateStatement = connection.prepareStatement(UPDATE_SQL);
		updateStatement.setString(1, model.getName());
		updateStatement.setString(2, model.getDescription());
		updateStatement.setDouble(3, model.getProgress());
		updateStatement.setInt(4, model.getId());
		updateStatement.executeUpdate();
		objectCache.put(model.getId(), model);
		updateStatement.close();
	}

	@Override
	public void delete(Difficulty model) throws SQLException {
		deleteStatement = connection.prepareStatement(DELETE_SQL);
		deleteStatement.setInt(1, model.getId());
		deleteStatement.executeUpdate();
		objectCache.remove(model.getId());
		deleteStatement.close();
	}

	@Override
	public int count(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int count() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Difficulty getObject(ResultSet resultSet) throws SQLException {
		int id=resultSet.getInt(Difficulty.ID);
		Difficulty model=objectCache.get(id);
		if (model==null) {
			model=new Difficulty();
			model.setId(id);
			model.setName(resultSet.getString(Difficulty.NAME));
			model.setDescription(resultSet.getString(Difficulty.DESCRIPTION));
			objectCache.put(id, model);
		}else {
			model.setName(resultSet.getString(Difficulty.NAME));
			model.setDescription(resultSet.getString(Difficulty.DESCRIPTION));	
		}
		return model;
	}

	@Override
	public boolean isClosed() throws SQLException {
		if (connection!=null) {
			return connection.isClosed();
		}
		return false;
	}

	@Override
	public void close() throws SQLException {
		if (connection != null) {
			if (!connection.isClosed()) {
				connection.close();
			}
			connection = null;
		}
	}

	@Override
	public void setConnection(Connection connection) {
		this.connection=connection;		
	}

}
